import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintStream;
import java.text.SimpleDateFormat;
import java.util.logging.Logger;
import java.util.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.Timestamp;

//import com.MSsql.jdbc.PreparedStatement;
import java.sql.*;


import twitter4j.*;
import twitter4j.auth.AccessToken;

public class tversecrawler {
	//Twitter setup
	//Using Twitter4j 2.2.5
	private static String sTwitterName;
	private static BufferedReader reader;
	private static Twitter twitter;
	private static ArrayList<String> locations = new ArrayList<String>();   
    private final static Logger logger = Logger.getLogger(tversecrawler.class.getName());
    
    private static FileOutputStream out; // declare a file output object
    private static PrintStream p; // declare a print stream object
    
    //msSQL setup
    //Using mssql connector 5.1.19
	private static Connection connect = null;
	private static Statement statement = null;	
	private static ResultSet resultSet = null;	
	private static ResultSet rsUserQuery = null;
	
	

	public static void main(String[] args) throws Exception {
		//Build our twitter api
		twitter = new TwitterFactory().getInstance();
		
		out = new FileOutputStream("output_torecover.txt", true);  
		p = new PrintStream(out);
		//  Check to make sure only a single file argument was provided
        /* Run the following code if you are reading from file
         if (args.length > 1 || args.length < 1)
                System.out.println ("Too many/few arguments. Correct usage: tversecrawler <twitterFollowers>");
         else if (args.length > 0){
                readFile(args[0]);        		
                try {
                       reader.close();
                } catch (IOException e) {
                       e.printStackTrace();
               } */
			readDB();	// run the this if you are fetching users from the database
			//readFile(args[0]); // run the this if you are fetching users from the file
            fetchTweetsByUser();
        //}
	}

	private static void openMSSqlConnection() throws Exception {		
		//String url = "jdbc:mysql://127.0.0.1:3306/tversecrawler";
		String url = "jdbc:sqlserver://127.0.0.1:1433;database=tversecrawler;integratedSecurity=true;";
		String username = "tversecrawler";
		String password = "Firehawk03";
				
		try {
			    System.out.println("Connecting database...");
			    connect = DriverManager.getConnection(url, username, password);
			    System.out.println("Database connected!");
			} catch (SQLException e) {
			    throw new RuntimeException("Cannot connect the database!", e);
		} 
			
	}
	
	private static void closeMSSqlConnection() {
        //Close the MSsql driver for our database
		try {
				if (resultSet != null) {
					resultSet.close();
				}
				if (statement != null) {
					statement.close();
				}
				if (connect != null) {
					connect.close();
				}
		} catch (Exception e) {

		}
	}
	
	private static void queryTweets(String location) throws Exception {	
		int totalTweets=0;	
		int lowPage = 1;
		int highPage = 200; 

		//Get the user name we are querying
		sTwitterName = location;
		logger.info("Retrieving tweets for : " + sTwitterName);
		p.println("Retrieving tweets for : " + sTwitterName);
		 
		Paging paging = new Paging(lowPage, highPage);		
		
		for (int i = 1; i < 5; i++) {
			List<Status> statuses = twitter.getUserTimeline(sTwitterName, paging);
			ListIterator<Status> li = statuses.listIterator();
	        
			openMSSqlConnection();
	        
			while (li.hasNext()) {	        		        	      		      	
	           Status st = li.next();
	           
	           //First check to see if we have already indexed this tweet	  
	           PreparedStatement stmtCheck = (PreparedStatement) connect.prepareStatement("select * from twt_tweets where tweet_id = '" + st.getId() + "';");
	           resultSet = stmtCheck.executeQuery();
	          
	           if (!resultSet.next()) {	           	                       
	            System.out.println("NEW TWEET");
	            totalTweets++;

	            //Build our timestamp for insert
	          	SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		        java.util.Date date= new java.util.Date();
		        System.out.println(new Timestamp(date.getTime()));
	 	        
	            //Insert our data into the database
	            String stmt = "insert into twt_tweets (tweet_id, retweet_ct, from_user, from_userId, to_user, to_userId, created_dt, txt, source, location, geolocation, place, insert_dt, access_level, annotations, replyto_screenname, replyto_statusid, ratelimit_status, retweeted_status, screen_name)" +
	            			" values (" + 
	            			"'"+st.getId()+"',"+
	            			"'"+st.getRetweetCount()+"',"+
	                        "'"+st.getUser().getName()+"',"+
	                        "'"+st.getUser().getId()+"',"+ 
	                        "'"+st.getAccessLevel()+"',"+
	                        "'"+st.getInReplyToUserId()+"',"+
	                        "'"+(df.format(st.getCreatedAt()))+"',"+
	                        "'"+(st.getText()).replace("'", ".")+"',"+	                       
	                        "'"+st.getSource()+"',"+
	                        "'"+st.getUser().getLocation()+"',"+    
	                        "'"+st.getGeoLocation()+"',"+   
	                        "'"+st.getPlace()+"',"+ 
	                        "'"+df.format(date.getTime())+"',"+   	//Tweet insert date	
	                        "'"+st.getAccessLevel()+"',"+
	                        "'"+st.getAnnotations()+"',"+
	                        "'"+st.getInReplyToScreenName()+"',"+
	                        "'"+st.getInReplyToStatusId()+"',"+
	                        "'"+st.getRateLimitStatus()+"',"+
	                        "'"+st.getRetweetedStatus()+"',"+
	                        "'"+st.getUser().getScreenName()+"')";
	            
	            System.out.println("SQL: " + stmt);	
	               
                try {
                    statement = connect.createStatement();
                    statement.execute(stmt);
                } catch (SQLException ex) {
                    System.out.println(ex);		           
               }
           }
	     }
        closeMSSqlConnection();
        logger.info("done! ");
		System.out.println("This run got total " + totalTweets + " tweets ");
		
		lowPage += 200;
		highPage += 200;
		paging = new Paging(lowPage, highPage);
		}		
	}

	private static void openConnection() {
		String consumerKey = "ulykiAisbBZmm3yO3Gljg";
		String consumerSecret = "oYevMEV6utATGS6ZXnk5oamlTgYQJ8W5d3TtKsIi4";
		String twitterToken = "535536803-slZu3SIYP5eZkpSPCt6orFaRfSUAxAu5l3VgQP95";
		String twitterTokenSecret = "S3FQxrLeggW7VjL71g2fj08K1RFkCDdEDVlcfStTNc";
		
		twitter.setOAuthConsumer(consumerKey, consumerSecret);
		AccessToken accessToken = new AccessToken(twitterToken, twitterTokenSecret);
	
		//Setup our connection
		twitter.setOAuthAccessToken(accessToken);
		//Now grab our twitter name to ensure we are working
		try {
			System.out.format ("Hello %s", twitter.getScreenName());
			logger.info("Connection successful!");
		} catch (IllegalStateException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (TwitterException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	private static void closeConnection() {
			System.out.println("Closing connection");
			logger.info("Closing Connection");
			//Shutdown the connection
    		twitter.shutdown();        	
	}

    private static void fetchTweetsByUser() throws Exception {
    	    long sleepTime = 60000; //1 min to start with
        	//Setup our Connection
        	openConnection();
        	//Now read each line in the file/db and query the tweets for each of those users
        	//Query the tweets from a user
        	for(int i=0; i<locations.size();i++) {
                 try {
                	 System.out.format("Querying %s times", locations.size());
                	 queryTweets(locations.get(i));
                 } catch (TwitterException e) {
                     if (e.exceededRateLimitation()) {
                         System.out.println("blocked!");
                         logger.info("blocked!");
                         sleepTime*=2; //double the sleeptime
                     }
                 } catch (SQLException e) {
                	 e.printStackTrace();  }
                 
                 try {
                     System.out.println("Sleeping for (minutes):" +(sleepTime/1000)/60);
                     Thread.sleep(sleepTime);
                     if (sleepTime / 1.5 > 60000) //dont fall below a minute
                         sleepTime/= 1.5; //lower the sleep time a little
                 } catch (InterruptedException e) {
                     e.printStackTrace(); }
	    	} 
			//Close our connection
	        closeConnection();
    }
   
    //To fetch user from database
    private static void readDB() throws Exception {
    	openMSSqlConnection();
    	
    	try{	
			PreparedStatement stmtCheck = (PreparedStatement) connect.prepareStatement("select distinct screen_name, id from twt_user_information where id > 16769;");
			rsUserQuery = stmtCheck.executeQuery();
	           
			while (rsUserQuery.next()){
					String ScreenName = rsUserQuery.getString("screen_name");
					String id = rsUserQuery.getString("id");
					System.out.println("ScreenName : " + ScreenName + " - Id: " +id);
					//System.out.println(id);
					p.println("ScreenName : " + ScreenName + " - Id: " +id);
					processTwitterUsers(ScreenName);
				}
			}
			catch (Exception e){
				System.err.println ("Error reading from file " + e.getMessage());
			}	
    }
    

	/**
	 ** Reads the locations file
	 * @param file
	 */
    private static void readFile(String file){
		String input = null;
		try{
			reader = new BufferedReader (new FileReader (file));
			input = reader.readLine();
			while (input != null){
					processTwitterUsers(input);
					input = reader.readLine();				
				}
			}
			catch (Exception e){
				System.err.println ("Error reading from file " + file + ": " + e.getMessage());
			}	
     }

    /**
     *
     * @param input
     */
    private static void processTwitterUsers(String input){
		//Add the read content to our Array List of strings
		locations.add(input);
		//System.out.format("Locations Size %s", locations.size());
	}    
}




